﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.IO;

using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.HPSF;


public class NPOIExportExclHelper
{
    /// <summary>
    /// 创建一个Excel
    /// Yakecan
    /// </summary>
    /// <returns>返回一个空表格</returns>
    public HSSFWorkbook InitializeWorkBook()
    {
        HSSFWorkbook workBook = new HSSFWorkbook();
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

        dsi.Company = "四川金仁医药集团有限公司";
        dsi.Manager = "Office Word 2003/2007";

        si.Author = "http://www.scjrm.com";
        si.Subject = "信息导出";
        si.Title = "系统报表";
        si.ApplicationName = "Microsoft Excel";
        si.CreateDateTime = DateTime.Now;
        si.LastAuthor = "金仁医药集团";

        workBook.DocumentSummaryInformation = dsi;
        workBook.SummaryInformation = si;

        return workBook;
    }


    /// <summary>
    /// 把指定的DataTable导出Excel
    /// Yakecan
    /// </summary>
    /// <param name="dt">数据源</param>
    /// <param name="path">导出的路径(包含文件的名称及后缀名)</param>
    /// <param name="tittle">Sheet的名称</param>
    public void Export(DataTable dt, string path, string tittle)
    {
        HSSFWorkbook workbook = InitializeWorkBook();
        ISheet sheet1 = workbook.CreateSheet(tittle);

        IRow titleRow = sheet1.CreateRow(0);
        titleRow.Height = (short)20 * 25;

        ICellStyle titleStyle = workbook.CreateCellStyle();
        titleStyle.Alignment = HorizontalAlignment.Center;
        titleStyle.VerticalAlignment = VerticalAlignment.Center;
        IFont font = workbook.CreateFont();
        font.FontName = "宋体";
        font.FontHeightInPoints = (short)16;
        titleStyle.SetFont(font);

        NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count);
        sheet1.AddMergedRegion(region); // 添加合并区域

        ICell titleCell = titleRow.CreateCell(0);
        titleCell.CellStyle = titleStyle;
        titleCell.SetCellValue(tittle);


        IRow headerRow = sheet1.CreateRow(1);
        ICellStyle headerStyle = workbook.CreateCellStyle();
        headerStyle.Alignment = HorizontalAlignment.Center;
        headerStyle.VerticalAlignment = VerticalAlignment.Center;
        headerStyle.BorderBottom = BorderStyle.Thin;
        headerStyle.BorderLeft = BorderStyle.Thin;
        headerStyle.BorderRight = BorderStyle.Thin;
        headerStyle.BorderTop = BorderStyle.Thin;
        IFont titleFont = workbook.CreateFont();
        titleFont.FontHeightInPoints = (short)11;
        titleFont.FontName = "宋体";
        headerStyle.SetFont(titleFont);

        headerRow.CreateCell(0).SetCellValue("序号");
        headerRow.GetCell(0).CellStyle = headerStyle;

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            headerRow.CreateCell(i + 1).SetCellValue(dt.Columns[i].ColumnName);
            headerRow.GetCell(i + 1).CellStyle = headerStyle;
            sheet1.SetColumnWidth(i, 256 * 18);
        }

        ICellStyle bodyStyle = workbook.CreateCellStyle();
        bodyStyle.BorderBottom = BorderStyle.Thin;
        bodyStyle.BorderLeft = BorderStyle.Thin;
        bodyStyle.BorderRight = BorderStyle.Thin;
        bodyStyle.BorderTop = BorderStyle.Thin;
        for (int r = 0; r < dt.Rows.Count; r++)
        {
            IRow bodyRow = sheet1.CreateRow(r + 2);
            bodyRow.CreateCell(0).SetCellValue(r + 1);
            bodyRow.GetCell(0).CellStyle = bodyStyle;
            bodyRow.GetCell(0).CellStyle.Alignment = HorizontalAlignment.Center;

            for (int c = 0; c < dt.Columns.Count; c++)
            {
                bodyRow.CreateCell(c + 1).SetCellValue(dt.Rows[r][c].ToString());
                bodyRow.GetCell(c + 1).CellStyle = bodyStyle;
            }
        }

        sheet1.CreateFreezePane(1, 2);        
        FileStream fs = new FileStream(path, FileMode.Create);
        workbook.Write(fs);
        fs.Flush();
        fs.Position = 0;         
        sheet1 = null;
        headerRow = null;
        workbook = null;
        //OutPutExcelStreamOnClient(ms, xlsName);        
        fs.Dispose();
         
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="dt">数据</param>
    /// <param name="path">路径</param>
    /// <param name="tittle">标题</param>
    /// <param name="line">导出列数</param>
    /// <returns></returns>
    public Stream RenderDataTableToExcel(DataTable dt, object[,] title, string tittle,int line)
        {
            HSSFWorkbook workbook = InitializeWorkBook();
            ISheet sheet1 = workbook.CreateSheet(tittle);

            IRow titleRow = sheet1.CreateRow(0);
            titleRow.Height = (short)25 * 25;
            
            ICellStyle titleStyle = workbook.CreateCellStyle();
            titleStyle.Alignment = HorizontalAlignment.Center;
            titleStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = (short)12;
            font.IsBold = true;
            titleStyle.SetFont(font);
            
            /*
            NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count);
            sheet1.AddMergedRegion(region); // 添加合并区域
            */
            
            //object[,] data = { { "序号","员工编号", "姓名","部门", "基本工资", "绩效分", "提成工资","加班工资","保险费","公积金" } };
           

            int rowCount = 1, columnCount = title.Length;
            IRow row;
            ICell cell = titleRow.CreateCell(0);
            
            for (int i = 0; i < rowCount; i++)
            {
                row = sheet1.CreateRow(i);//创建第i行
                for (int j = 0; j < columnCount; j++)
                {
                    cell = titleRow.CreateCell(j);//创建第j列
                    object obj = title[i, j];
                    cell.SetCellValue(obj.ToString());
                    cell.CellStyle = titleStyle;
                }
            }

            IDataFormat dataformat = workbook.CreateDataFormat();
            ICellStyle bodyStyle = workbook.CreateCellStyle();
            bodyStyle.BorderBottom = BorderStyle.Thin;
            bodyStyle.BorderLeft = BorderStyle.Thin;
            bodyStyle.BorderRight = BorderStyle.Thin;
            bodyStyle.BorderTop = BorderStyle.Thin;
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                IRow bodyRow = sheet1.CreateRow(r + 1);
                bodyRow.CreateCell(0).SetCellValue(r + 1);
                bodyRow.GetCell(0).CellStyle = bodyStyle;
                bodyRow.GetCell(0).CellStyle.Alignment = HorizontalAlignment.Center;

                ICellStyle style = workbook.CreateCellStyle();
                style.DataFormat = dataformat.GetFormat("0.00;[Red]-0.00");
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                
                
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    
                    if (c + 1 >= line)
                    {
                       // if (Convert.ToDouble(dt.Rows[r][c].ToString()) == 0)
                       // {
                            bodyRow.CreateCell(c + 1).SetCellValue("");
                      //  }
                     //   else
                     //   {
                          //  bodyRow.CreateCell(c + 1).SetCellValue(Convert.ToDouble(dt.Rows[r][c].ToString()));
                    //    }
                        bodyRow.GetCell(c + 1).CellStyle = style;
                    }
                    else
                    {
                        bodyRow.CreateCell(c + 1).SetCellValue(dt.Rows[r][c].ToString());
                        bodyRow.GetCell(c + 1).CellStyle = bodyStyle;
                    }
                    
                }
            }

            sheet1.CreateFreezePane(7, 1);

            ISheet sheet2 = workbook.CreateSheet("模板填写说明");
            sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 10));
            sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 10));
            sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 10));
            sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 10));
            sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 0, 10));
            sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 0, 10));

            sheet2.CreateRow(0).CreateCell(0).SetCellValue("模板填写说明");
            sheet2.CreateRow(1).CreateCell(0).SetCellValue("个人绩效请直接填绩效分");
            sheet2.CreateRow(2).CreateCell(0).SetCellValue("提成、其他工资、水电费请直接填写金额");
            sheet2.CreateRow(3).CreateCell(0).SetCellValue("加班天数请填写天数、加班时长请填写小时数、其它加班请直接填写金额");
            sheet2.CreateRow(4).CreateCell(0).SetCellValue("迟到、早退、上班未打卡、下班未打卡请填写次数");
            sheet2.CreateRow(5).CreateCell(0).SetCellValue("事假、病假、年假请填写天数，不足1天的折算成小数填写");
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet1 = null;
            //headerRow = null;
            workbook = null;

            ms.Dispose();
            return ms;
        }
}